using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Common;
using System.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using Musicman.Entities;
using Musicman.Common;
namespace Musicman.DAL
{
	public static class SearchjobDAO
	{
        #region Insert
        private static DbCommand insertCommand = null;
        private static string insertSQL = "INSERT INTO SearchJob (NAME, ALLCRITERIA, TYPE, STARTTIME, ENDTIME, TOTALFILES, TOTALLENGTH) VALUES (@NAME, @ALLCRITERIA, @TYPE, @STARTTIME, @ENDTIME, @TOTALFILES, @TOTALLENGTH) SET @SEARCHJOBID = SCOPE_IDENTITY()";
        private static DbCommand GetInsertCommand(SqlDatabase db)
        {
            if (insertCommand == null)
            {
                insertCommand = db.GetSqlStringCommand(insertSQL);
                db.AddOutParameter(insertCommand, "@SEARCHJOBID", DbType.Int64, 8);
                db.AddInParameter(insertCommand, "@NAME", DbType.String);
                db.AddInParameter(insertCommand, "@ALLCRITERIA", DbType.String);
                db.AddInParameter(insertCommand, "@TYPE", DbType.Byte);
                db.AddInParameter(insertCommand, "@STARTTIME", DbType.DateTime);
                db.AddInParameter(insertCommand, "@ENDTIME", DbType.DateTime);
                db.AddInParameter(insertCommand, "@TOTALFILES", DbType.Int64);
                db.AddInParameter(insertCommand, "@TOTALLENGTH", DbType.Int64);
            }
            return insertCommand;
        }
        private static void SetInsertValues(SqlDatabase db, DbCommand cmd, Searchjob entity)
        {
            db.SetParameterValue(cmd, "@NAME", entity.Name);
            db.SetParameterValue(cmd, "@ALLCRITERIA", entity.Allcriteria);
            db.SetParameterValue(cmd, "@TYPE", entity.Type);
            db.SetParameterValue(cmd, "@STARTTIME", entity.Starttime);
            db.SetParameterValue(cmd, "@ENDTIME", entity.Endtime);
            db.SetParameterValue(cmd, "@TOTALFILES", entity.Totalfiles);
            db.SetParameterValue(cmd, "@TOTALLENGTH", entity.TotalLength);
        }
        public static bool InsertSearchjob(Searchjob entity)
        {
            bool res = false;
            SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.MusicmanConnectionString));
            DbCommand cmd = GetInsertCommand(db);
            SetInsertValues(db, cmd, entity);
            res = Convert.ToBoolean(db.ExecuteNonQuery(cmd));
            if (res)
            {
                entity.Searchjobid = (long?)db.GetParameterValue(cmd, "@SEARCHJOBID");
            }
            return res;
        }
        public static bool InsertSearchjob(Searchjob entity, DbTransaction transactionObj, SqlDatabase db)
        {
            bool res = false;
            DbCommand cmd = GetInsertCommand(db);
            SetInsertValues(db, cmd, entity);
            res = Convert.ToBoolean(db.ExecuteNonQuery(cmd, transactionObj));
            if (res)
            {
                entity.Searchjobid = (long?)db.GetParameterValue(cmd, "@SEARCHJOBID");
            }
            return res;
        }
        public static bool InsertListSearchjob(List<Searchjob> list)
        {
            bool res = true;
            DbTransaction transactionObj = null;
            DbConnection conn = null;
            try
            {
                SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.MusicmanConnectionString));
                DbCommand cmd = GetInsertCommand(db);
                conn = db.CreateConnection();
                conn.Open();
                transactionObj = conn.BeginTransaction();
                for (int i = 0; i < list.Count; i++)
                {
                    Searchjob entity = list[i];
                    SetInsertValues(db, cmd, entity);
                    res = res & Convert.ToBoolean(db.ExecuteNonQuery(cmd, transactionObj));
                    if (res)
                    {
                        entity.Searchjobid = (long?)db.GetParameterValue(cmd, "@SEARCHJOBID");
                    }
                }
                transactionObj.Commit();
                transactionObj.Dispose();
                conn.Close();
                conn.Dispose();
            }
            catch (Exception ex)
            {
                res = false;
                if (transactionObj != null)
                {
                    transactionObj.Rollback();
                    transactionObj.Dispose();
                }
                if (conn != null & conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                    conn.Dispose();
                }
                throw ex;
            }
            return res;
        }
        public static bool InsertListSearchjob(List<Searchjob> list, DbTransaction transactionObj, SqlDatabase db)
        {
            bool res = true;
            DbCommand cmd = GetInsertCommand(db);
            for (int i = 0; i < list.Count; i++)
            {
                Searchjob entity = list[i];
                SetInsertValues(db, cmd, entity);
                res = res & Convert.ToBoolean(db.ExecuteNonQuery(cmd, transactionObj));
                if (res)
                {
                    entity.Searchjobid = (long?)db.GetParameterValue(cmd, "@SEARCHJOBID");
                }
            }
            return res;
        }
        #endregion
        #region Update
        private static DbCommand updateCommand = null;
        private static string updateSQL = "UPDATE SearchJob SET NAME = @NAME, ALLCRITERIA = @ALLCRITERIA, TYPE = @TYPE, STARTTIME = @STARTTIME, ENDTIME = @ENDTIME, TOTALFILES = @TOTALFILES, TOTALLENGTH = @TOTALLENGTH WHERE SEARCHJOBID = @SEARCHJOBID";
        private static DbCommand GetUpdateCommand(SqlDatabase db)
        {
            if (updateCommand == null)
            {
                updateCommand = db.GetSqlStringCommand(updateSQL);
                db.AddInParameter(updateCommand, "@SEARCHJOBID", DbType.Int64);
                db.AddInParameter(updateCommand, "@NAME", DbType.String);
                db.AddInParameter(updateCommand, "@ALLCRITERIA", DbType.String);
                db.AddInParameter(updateCommand, "@TYPE", DbType.Byte);
                db.AddInParameter(updateCommand, "@STARTTIME", DbType.DateTime);
                db.AddInParameter(updateCommand, "@ENDTIME", DbType.DateTime);
                db.AddInParameter(updateCommand, "@TOTALFILES", DbType.Int64);
                db.AddInParameter(updateCommand, "@TOTALLENGTH", DbType.Int64);
            }
            return updateCommand;
        }
        private static void SetUpdateValues(SqlDatabase db, DbCommand cmd, Searchjob entity)
        {
            db.SetParameterValue(cmd, "@SEARCHJOBID", entity.Searchjobid);
            db.SetParameterValue(cmd, "@NAME", entity.Name);
            db.SetParameterValue(cmd, "@ALLCRITERIA", entity.Allcriteria);
            db.SetParameterValue(cmd, "@TYPE", entity.Type);
            db.SetParameterValue(cmd, "@STARTTIME", entity.Starttime);
            db.SetParameterValue(cmd, "@ENDTIME", entity.Endtime);
            db.SetParameterValue(cmd, "@TOTALFILES", entity.Totalfiles);
            db.SetParameterValue(cmd, "@TOTALLENGTH", entity.TotalLength);
        }
        public static bool UpdateSearchjob(Searchjob entity)
        {
            bool res = false;
            SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.MusicmanConnectionString));
            DbCommand cmd = GetUpdateCommand(db);
            SetUpdateValues(db, cmd, entity);
            res = Convert.ToBoolean(db.ExecuteNonQuery(cmd));
            return res;
        }
        public static bool UpdateSearchjob(Searchjob entity, DbTransaction transactionObj, SqlDatabase db)
        {
            bool res = false;
            DbCommand cmd = GetUpdateCommand(db);
            SetUpdateValues(db, cmd, entity);
            res = Convert.ToBoolean(db.ExecuteNonQuery(cmd, transactionObj));
            return res;
        }
        public static bool UpdateListSearchjob(List<Searchjob> list)
        {
            bool res = true;
            DbTransaction transactionObj = null;
            DbConnection conn = null;
            try
            {
                SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.MusicmanConnectionString));
                DbCommand cmd = GetUpdateCommand(db);
                conn = db.CreateConnection();
                conn.Open();
                transactionObj = conn.BeginTransaction();
                for (int i = 0; i < list.Count; i++)
                {
                    Searchjob entity = list[i];
                    SetUpdateValues(db, cmd, entity);
                    res = res & Convert.ToBoolean(db.ExecuteNonQuery(cmd, transactionObj));
                }
                transactionObj.Commit();
                transactionObj.Dispose();
                conn.Close();
                conn.Dispose();
            }
            catch (Exception ex)
            {
                res = false;
                if (transactionObj != null)
                {
                    transactionObj.Rollback();
                    transactionObj.Dispose();
                }
                if (conn != null & conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                    conn.Dispose();
                }
                throw ex;
            }
            return res;
        }
        public static bool UpdateListSearchjob(List<Searchjob> list, DbTransaction transactionObj, SqlDatabase db)
        {
            bool res = true;
            DbCommand cmd = GetUpdateCommand(db);
            for (int i = 0; i < list.Count; i++)
            {
                Searchjob entity = list[i];
                SetUpdateValues(db, cmd, entity);
                res = res & Convert.ToBoolean(db.ExecuteNonQuery(cmd, transactionObj));
            }
            return res;
        }

        #endregion
		#region Save

        public static bool SaveSearchjob(Searchjob entity)
        {
            return SaveSearchjob(entity, false);
        }

		public static bool SaveSearchjob(Searchjob entity, bool saveLocations)
		{
            bool res = true;
            DbTransaction transactionObj = null;
            DbConnection conn = null;
            try
            {
                SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.MusicmanConnectionString));
                DbCommand cmd = GetUpdateCommand(db);
                conn = db.CreateConnection();
                conn.Open();
                transactionObj = conn.BeginTransaction();

                if (entity.Searchjobid == null)
                {
                    res = InsertSearchjob(entity);
                }
                else
                {
                    res = UpdateSearchjob(entity);
                }
                if (saveLocations)
                {
                    res = res & SearchlocationDAO.SaveListSearchlocation(entity.Searchlocations, transactionObj, db, true);
                }
                transactionObj.Commit();
                transactionObj.Dispose();
                conn.Close();
                conn.Dispose();
            }
            catch (Exception ex)
            {
                res = false;
                if (transactionObj != null)
                {
                    transactionObj.Rollback();
                    transactionObj.Dispose();
                }
                if (conn != null & conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                    conn.Dispose();
                }
                throw ex;
            }
			return res;
		}

        public static bool SaveSearchjob(Searchjob entity, DbTransaction transactionObj, SqlDatabase db)
        {
            return SaveSearchjob(entity, transactionObj, db, false);
        }

        public static bool SaveSearchjob(Searchjob entity, DbTransaction transactionObj, SqlDatabase db, bool saveLocations)
		{
			bool res = true;
			if (entity.Searchjobid == null )
			{
				res = InsertSearchjob(entity, transactionObj, db);
			}
			else
			{
				res = UpdateSearchjob(entity, transactionObj, db);
			}
            if (saveLocations)
            {
                res = res & SearchlocationDAO.SaveListSearchlocation(entity.Searchlocations, transactionObj, db, true);
            }
			return res;
		}

        public static bool SaveListSearchjob(List<Searchjob> list)
        {
            return SaveListSearchjob(list, false);
        }

        public static bool SaveListSearchjob(List<Searchjob> list, bool saveLocations)
		{
			bool res = true;
			DbTransaction transactionObj = null;
			DbConnection conn = null;
			try
			{
				SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.MusicmanConnectionString));
				conn = db.CreateConnection();
				conn.Open();
				transactionObj = conn.BeginTransaction();
				for(int i=0; i<list.Count; i++)
				{
					Searchjob entity = list[i];
					res = res & SaveSearchjob(entity, transactionObj, db);
                    if (saveLocations)
                    {
                        res = res & SearchlocationDAO.SaveListSearchlocation(entity.Searchlocations, transactionObj, db, true);
                    }
				}
				transactionObj.Commit();
				transactionObj.Dispose();
				conn.Close();
				conn.Dispose();
			}
			catch (Exception ex)
			{
				res = false;
				if (transactionObj != null)
				{
					transactionObj.Rollback();
					transactionObj.Dispose();
				}
				if (conn != null & conn.State != ConnectionState.Closed)
				{
					conn.Close();
					conn.Dispose();
				}
				throw ex;
			}
			return res;
		}

        public static bool SaveListSearchjob(List<Searchjob> list, DbTransaction transactionObj, SqlDatabase db)
        {
            return SaveListSearchjob(list, transactionObj, db, false);
        }

        public static bool SaveListSearchjob(List<Searchjob> list, DbTransaction transactionObj, SqlDatabase db, bool saveLocations)
		{
			bool res = true;
			for(int i=0; i<list.Count; i++)
			{
				Searchjob entity = list[i];
				res = res & SaveSearchjob(entity, transactionObj, db);
                if (saveLocations)
                {
                    res = res & SearchlocationDAO.SaveListSearchlocation(entity.Searchlocations, transactionObj, db, true);
                }
			}
			return res;
		}
		#endregion
        #region Delete
        private static DbCommand deleteCommand = null;
        private static string deleteSQL = "DELETE FROM SearchJob WHERE SEARCHJOBID = @SEARCHJOBID";
        private static DbCommand GetDeleteCommand(SqlDatabase db)
        {
            if (deleteCommand == null)
            {
                deleteCommand = db.GetSqlStringCommand(deleteSQL);
                db.AddInParameter(deleteCommand, "@SEARCHJOBID", DbType.Int64);
            }
            return deleteCommand;
        }
        private static void SetDeleteValues(SqlDatabase db, DbCommand cmd, Searchjob entity)
        {
            db.SetParameterValue(cmd, "@SEARCHJOBID", entity.Searchjobid);
        }
        public static bool DeleteSearchjob(Searchjob entity)
        {
            bool res = false;
            SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.MusicmanConnectionString));
            DbCommand cmd = GetDeleteCommand(db);
            SetDeleteValues(db, cmd, entity);
            res = Convert.ToBoolean(db.ExecuteNonQuery(cmd));
            return res;
        }
        public static bool DeleteListSearchjob(List<Searchjob> list)
        {
            bool res = true;
            DbTransaction transactionObj = null;
            DbConnection conn = null;
            try
            {
                SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.MusicmanConnectionString));
                DbCommand cmd = GetDeleteCommand(db);
                conn = db.CreateConnection();
                conn.Open();
                transactionObj = conn.BeginTransaction();
                for (int i = 0; i < list.Count; i++)
                {
                    Searchjob entity = list[i];
                    SetDeleteValues(db, cmd, entity);
                    res = res & Convert.ToBoolean(db.ExecuteNonQuery(cmd, transactionObj));
                }
                transactionObj.Commit();
                transactionObj.Dispose();
                conn.Close();
                conn.Dispose();
            }
            catch (Exception ex)
            {
                res = false;
                if (transactionObj != null)
                {
                    transactionObj.Rollback();
                    transactionObj.Dispose();
                }
                if (conn != null & conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                    conn.Dispose();
                }
                throw ex;
            }
            return res;
        }

        public static void ClearAllData()
        {
            SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.MusicmanConnectionString));
            db.ExecuteNonQuery(CommandType.StoredProcedure, "ClearPreviousResults");
        }

        public static void ClearJobData(Searchjob job)
        {
            SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.MusicmanConnectionString));
            DbCommand cmd = db.GetStoredProcCommand("ClearJobData");
            db.AddInParameter(cmd, "@SearchJobID", DbType.Int64, job.Searchjobid);
            db.ExecuteNonQuery(cmd);
        }

        #endregion

        #region Select
        private static string selectSQL = "SELECT SEARCHJOBID,NAME,ALLCRITERIA,TYPE,STARTTIME,ENDTIME,TOTALFILES, TOTALLENGTH FROM SearchJob {0} {1}";

        
        public static List<Searchjob> LoadSearchjobs(string where, string orderby)
        {
            List<Searchjob> res = new List<Searchjob>();
            string sql = String.Format(selectSQL, where, orderby);
            IDataReader reader = null;
            try
            {
                SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.MusicmanConnectionString));
                reader = db.ExecuteReader(CommandType.Text, sql);
                if (reader != null)
                {
                    while (reader.Read())
                    {
                        Searchjob entity = new Searchjob();
                        entity.Searchjobid = reader.IsDBNull(0) ? null : (long?)reader.GetInt64(0);
                        entity.Name = reader.IsDBNull(1) ? null : (string)reader.GetString(1);
                        entity.Allcriteria = reader.IsDBNull(2) ? null : (string)reader.GetString(2);
                        entity.Type = reader.IsDBNull(3) ? null : (byte?)reader.GetByte(3);
                        entity.Starttime = reader.IsDBNull(4) ? null : (DateTime?)reader.GetDateTime(4);
                        entity.Endtime = reader.IsDBNull(5) ? null : (DateTime?)reader.GetDateTime(5);
                        entity.Totalfiles = reader.IsDBNull(6) ? null : (long?)reader.GetInt64(6);
                        entity.TotalLength = reader.IsDBNull(7) ? null : (long?)reader.GetInt64(7);
                        res.Add(entity);
                    }
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                {
                    reader.Close();
                }
                res = null;
                throw ex;
            }
            return res;
        }

        public static List<Searchjob> LoadSearchjobsAllData(string where, string orderby)
        {
            List<Searchjob> res = new List<Searchjob>();
            string sql = String.Format(selectSQL, where, orderby);
            IDataReader reader = null;
            try
            {
                SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.MusicmanConnectionString));
                reader = db.ExecuteReader(CommandType.Text, sql);
                if (reader != null)
                {
                    while (reader.Read())
                    {
                        Searchjob entity = new Searchjob();
                        entity.Searchjobid = reader.IsDBNull(0) ? null : (long?)reader.GetInt64(0);
                        entity.Name = reader.IsDBNull(1) ? null : (string)reader.GetString(1);
                        entity.Allcriteria = reader.IsDBNull(2) ? null : (string)reader.GetString(2);
                        entity.Type = reader.IsDBNull(3) ? null : (byte?)reader.GetByte(3);
                        entity.Starttime = reader.IsDBNull(4) ? null : (DateTime?)reader.GetDateTime(4);
                        entity.Endtime = reader.IsDBNull(5) ? null : (DateTime?)reader.GetDateTime(5);
                        entity.Totalfiles = reader.IsDBNull(6) ? null : (long?)reader.GetInt64(6);
                        where = String.Format(" WHERE SEARCHJOBID = {0}", entity.Searchjobid);
                        List<Searchlocation> locations = SearchlocationDAO.LoadSearchlocations(where, "");
                        for (int i = 0; i < locations.Count; i++)
                        {
                            Searchlocation sl = locations[i];
                            where = String.Format(" WHERE SEARCHLOCATIONID = {0}", sl.Searchlocationid);
                            List<Searchfolder> folders = SearchfolderDAO.LoadSearchfolders(where, "");
                            for (int j = 0; j < folders.Count; j++)
                            {
                                Searchfolder sf = folders[j];
                                where = String.Format(" WHERE SEARCHFOLDERID = {0}", sf.Searchfolderid);
                                List<Searchfile> files = SearchfileDAO.LoadSearchfiles(where, "");
                                for (int k = 0; k < files.Count; k++)
                                {
                                    Searchfile file = files[k];
                                    sf.AddSearchFile(file);
                                    entity.Files.Add(file);
                                }
                                sl.AddSearchfolder(sf);
                            }
                            entity.AddSearchLocation(sl); 
                        }
                        
                        res.Add(entity);
                    }
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                {
                    reader.Close();
                }
                res = null;
                throw ex;
            }
            return res;
        }
        #endregion


	}
}
